# -*- coding: utf-8 -*-
# @Time    : 2022/7/19 14:15
# @Author  : Yanfeng
# @Email   : sxyanfeng2014@163.com
# @File    : db_utils.py
# @Software: PyCharm



import json
from datetime import datetime
from typing import Union

import pymysql
from util.readFileUtils.yamlControl import GetYamlData



class DB:
    mysql = GetYamlData().get_yaml_data()["database"]

    def __init__(self):
        """
        初始化数据库连接，并指定查询的结果集以字典形式返回
        """
        self.connection = pymysql.connect(
            host=self.mysql['host'],
            port=self.mysql['port'],
            user=self.mysql['user'],
            password=self.mysql['password'],
            db=self.mysql['db_name']
        )

    def execute_sql(self, sql: str,value: str) -> Union[dict, None]:
        """
        执行sql语句方法，查询所有结果的sql只会返回一条结果（
        比如说： 使用select * from cases , 结果将只会返回第一条数据    {'id': 1, 'name': 'updatehahaha', 'path': None, 'body': None, 'expected': '{"msg": "你好"}', 'api_id': 1, 'create_at': '2021-05-17 17:23:54', 'update_at': '2021-05-17 17:23:54'}

        ），支持select， delete， insert， update
        :param sql: sql语句
        :return: select 语句 如果有结果则会返回 对应结果字典，delete，insert，update 将返回None
        """
        with self.connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql,value)
            result = cursor.fetchone()
            # 使用commit解决查询数据出现概率查错问题
            self.connection.commit()
            return self.verify(result)
    def execute_sql_many(self,sql: str):
        """

        :param sql:
        :return:
        """
        with self.connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql)
            result = cursor.fetchall()
            self.connection.commit()
            return result

    def execute_sql_many1(self,sql: str,value):
        """

        :param sql:
        :return:
        """
        with self.connection.cursor(cursor=pymysql.cursors.DictCursor) as cursor:
            cursor.execute(sql,value)
            result = cursor.fetchall()
            self.connection.commit()
            return result
    def verify(self, result: dict) -> Union[dict, None]:
        """验证结果能否被json.dumps序列化"""
        # 尝试变成字符串，解决datetime 无法被json 序列化问题
        try:
            json.dumps(result)
        except TypeError:   # TypeError: Object of type datetime is not JSON serializable
            for k, v in result.items():
                if isinstance(v, datetime):
                    result[k] = str(v)
        return result

    def close(self):
        """关闭数据库连接"""
        self.connection.close()


if __name__ == '__main__':
    sql1 = "SELECT AttributeId FROM m_allegro_attribute_value WHERE CustomerId=1 AND CategoryId=100006 GROUP BY AttributeId;"
    db = DB()
    #查询所有的审核不通过的sku
    # sql2 = 'SELECT Sku FROM p_market_product_review WHERE CustomerId=1 AND MarketId=81 AND SiteCode="AU" AND ReviewStatus=%s'
    res1 = db.execute_sql_many(sql1)
    print(res1)
    # res2 = db.execute_sql_many1(sql2,(4,))
    # print(res2)
    # print(res1)
    # print(type(res1))
    # print(res2)
    # def get_dict(res):
    #     list1=[]
    #     for i in res:
    #        list1.append(i['Sku'])
    #     return list1
    # res11 = get_dict(res1)
    # res22 = get_dict(res2)
    #
    # def similarity(a, b):
    #     return [item for item in a if item in b]
    #
    # res = similarity(res11,res22)
    # print(res)
